#delimit;
clear;
capture log close;
set more off;
/*****local
CHANGE PATH
*****/;

local out "/data";
local path3 "/data";
local pathtab "/data";
local path2 "/data";
local pathtables "/data";


use "`out'/tu_panel.dta", clear;

gen state_2010_2014=state if year>=2010 & year<=2014;
sort pid year;
drop ZIP97 cbsa county lat llong state tract suffix ZIP99 ZIP01 employer occ llongv 
llongl msa block minpct pctin903 min inc blkgroup ZIP_07 STATEchar_07 CBSA07 StateFIPSCode07
CountyFIPSCode07 CensusTract07 CensusSuffix07 CensusBlockGroup07 Latitude07 Longitude07 
ZIP_08 STATEchar_08 CBSA08 StateFIPSCode08 CountyFIPSCode08 CensusTract08 CensusSuffix08 
CensusBlockGroup08 Latitude08 Longitude08 CountyFIPSCode CensusTract CensusSuffix 
CensusBlockGroup Latitude Longitude ZIPCODE10num ZIP10;

capture drop _merge;
merge 1:1 pid year using "`path2'/final.dta"; /*contains all location variables from TU for years 1997 to 2010*/
drop _merge;

replace state=string(state_2010_2014) if year>=2010 & year<=2014;
drop state_2010_2014;

/*I will create some indicators that say whether the person has a credit file or not in
each year*/
gen in1997t=(G104~=. & year==1997);
gen in1999t=(G104~=. & year==1999);
gen in2001t=(G104~=. & year==2001);
gen in2003t=(G104~=. & year==2003);
gen in2004t=(G104~=. & year==2004);
gen in2007t=(G104~=. & year==2007);
gen in2008t=(G104~=. & year==2008); 
gen in2010t=(G104~=. & year==2010); 
gen in2012t=(G104~=. & year==2012); 
gen in2014t=(G104~=. & year==2014); 


***********************************************************
/*****For Chart. Credit limit by credit score*******/;
preserve;
keep pid tr_am ccard_indiv_joint_lim_nm;
replace ccard_indiv_joint_lim_nm=0 if tr_am>=270 & tr_am<=900 & ccard_indiv_joint_lim_nm==.;
keep if tr_am>=270 & tr_am<=900;

gen scoregroup=.;

forvalues i=1/18 {;
	replace scoregroup=`i' if tr_am>=270+(`i'-1)*35 & tr_am<270+(`i')*35;
};
gen aa=1;
collapse (mean) ccard_indiv_joint_lim_nm (sum) aa, by(scoregroup);
gen score=.;
replace score=270+scoregroup*35-35/2;

export excel score ccard_indiv_joint_lim_nm using "`pathtables'/Credit_Limits_by_Score.xlsx", firstrow(variables) replace;
restore;
/*****************************************/
***********************************************************

***********************************************************
/*****For Chart. Credit limit by credit score*******/;
preserve;
keep pid tr_am ccard_indiv_joint_lim_nm;
keep if ccard_indiv_joint_lim_nm>0 & ccard_indiv_joint_lim_nm<.;
keep if tr_am>=270 & tr_am<=900 & ccard_indiv_joint_lim_nm~=.;
gen scoregroup=.;
forvalues i=1/18 {;
	replace scoregroup=`i' if tr_am>=270+(`i'-1)*35 & tr_am<270+(`i')*35;
};
gen aa=1;
collapse (mean) ccard_indiv_joint_lim_nm (sum) aa, by(scoregroup);
gen score=.;
replace score=270+scoregroup*35-35/2;

export excel score ccard_indiv_joint_lim_nm using "`pathtables'/Credit_Limits_PositiveLim_by_Score.xlsx", firstrow(variables) replace;
restore;

/*****************************************/
***********************************************************;

/**************For summary statistics table*******/;
preserve;
capture log close;
log using "`pathtables'/Table_1_cont2.log", replace;

/*TU never sent the records for these following people to NSC, so we should drop them from all regressions*/;
gen todrop=(pid=="CQ372100006084" | pid=="CQ372100009510" | pid=="CQ372200002745" | 
pid=="CQ372200003550" | pid=="CQ372400000387" | pid=="CQ372400000600" | pid=="CQ372400001543" | 
pid=="CQ372400003695" | pid=="CQ372400004137" | pid=="CQ372400005128" | pid=="CQ372400005720" | 
pid=="CQ372400006424" | pid=="CQ372500000301" | pid=="CQ372500000380" | pid=="CQ372500001977" | 
pid=="CQ372500006063" | pid=="CQ372500007637" | pid=="CQ372500008864" | pid=="CQ372600001756" |
pid=="CQ372600004821" | pid=="CQ372700001705" | pid=="CQ372700002435" | pid=="CQ372700003380" | 
pid=="CQ372700006709" | pid=="CQ372700006975" | pid=="CQ372800000154" | pid=="CQ372800005549" | 
pid=="CQ372900002337" | pid=="CQ372900004187" | pid=="CQ372900005785" | pid=="CQ372900008630" | 
pid=="CQ373100001269" | pid=="CQ373100001603" | pid=="CQ373100006188" | pid=="CQ373200000787" | 
pid=="CQ373200001594" | pid=="CQ373200002688" | pid=="CQ373200004182" | pid=="CQ373200004581" |
pid=="CQ373200004691" | pid=="CQ373200006989" | pid=="CQ373200007213" | pid=="CQ373200008162" | 
pid=="CQ373200008733" | pid=="CQ373300000792" | pid=="CQ373300002947" | pid=="CQ373300004204" | 
pid=="CQ373300004205" | pid=="CQ373400000626" | pid=="CQ373400002904" | pid=="CQ373500000720" | 
pid=="CQ373600004108" | pid=="CQ373600004759" | pid=="CQ373600006182" | pid=="CQ373600007420" | 
pid=="CQ373700002243" | pid=="CQ373700002715" | pid=="CQ373700005844" | pid=="CQ373700009096" |
pid=="CQ373800002129" | pid=="CQ373800002271" | pid=="CQ373800002364" | pid=="CQ373900003633" | 
pid=="CQ373900006494" | pid=="CQ373900007014" | pid=="9EA00300005576" | pid=="9EA00400005572" | 
pid=="9EA00500002667" | pid=="9EA00500005465" | pid=="9EA00700007009" | pid=="9EA00800009275" | 
pid=="CQ370200000953" | pid=="CQ370200001939" | pid=="CQ370200003277" | pid=="CQ370300005225" | 
pid=="CQ370300007037" | pid=="CQ370300008687" | pid=="CQ370400003593" | pid=="CQ370400005529" |
pid=="CQ370400007514" | pid=="CQ370400008412" | pid=="CQ370500001079" | pid=="CQ370500001258" | 
pid=="CQ370500003356" | pid=="CQ370500006624" | pid=="CQ370500006805" | pid=="CQ370500007723" | 
pid=="CQ370500008189" | pid=="CQ370600001023" | pid=="CQ370600008519" | pid=="CQ370600010249" | 
pid=="CQ370700001187" | pid=="CQ370700004542" | pid=="CQ370700005882" | pid=="CQ370800000424" | 
pid=="CQ370800005683" | pid=="CQ370800006851" | pid=="CQ370900001216" | pid=="CQ370900001674" |
pid=="CQ370900002376" | pid=="CQ370900005657" | pid=="CQ370900008437" | pid=="CQ371100002719" | 
pid=="CQ371100003439" | pid=="CQ371100003597" | pid=="CQ371100004633" | pid=="CQ371100005460" | 
pid=="CQ371100008563" | pid=="CQ371100009361" | pid=="CQ371200004861" | pid=="CQ371200008210" | 
pid=="CQ371200008915" | pid=="CQ371300001384" | pid=="CQ371300001647" | pid=="CQ371300001711" | 
pid=="CQ371300001875" | pid=="CQ371300002833" | pid=="CQ371300004506" | pid=="CQ371300006181" |
pid=="CQ371400004368" | pid=="CQ371400005464" | pid=="CQ371400005694" | pid=="CQ371400007372" | 
pid=="CQ371500000124" | pid=="CQ371500003405" | pid=="CQ371500006187" | pid=="CQ371500007891" | 
pid=="CQ371600000271" | pid=="CQ371600006663" | pid=="CQ371700000393" | pid=="CQ371700003661" | 
pid=="CQ371700004893" | pid=="CQ371700005284" | pid=="CQ371700006966" | pid=="CQ371800001716" | 
pid=="CQ371800004333" | pid=="CQ371800006108" | pid=="CQ371800007280" | pid=="CQ371800007646" |
pid=="CQ371800008647" | pid=="CQ371900003690");

local var="student mortgage auto ccard_indiv_joint install lcred unsec secgoods lease";
sum auto_debt if auto_debt<100000 & todrop==0;
sum install_debt if install_debt<100000 & todrop==0;
sum secgoods_debt if secgoods_debt<100000 & todrop==0;
sum ccard_indiv_joint_debt if ccard_indiv_joint_debt<100000 & todrop==0;

capture log close;
restore;
/*************************************************/;



sort pid year;
foreach v of varlist student_start* student_end* mortgage_start* mortgage_end* auto_start* auto_end* ccard_start* ccard_end* install_start* install_end* lcred_start* lcred_end* unsec_start* unsec_end* secgoods_start* secgoods_end* lease_start* lease_end* {;
by pid: egen `v't=max(`v');
drop `v';
rename `v't `v';
format `v' %td;
};

capture drop _merge;
sort pid;

/*Merging data with information for first school ever enrolled in NSC*/
capture drop _merge;
sort pid;
merge m:1 pid using "`pathtab'/First_College_NSC.dta";
drop if _merge==2;
drop _merge;

/*Merging data with information for first school ever enrolled in NSLDS*/
capture drop _merge;
sort pid;
merge m:1 pid using "`pathtab'/first_col_nslds_state.dta";
drop if _merge==2;
drop _merge;

/*Merging data with information for first school ever enrolled in NSLDS based on Pell grants*/
capture drop _merge;
sort pid;
merge m:1 pid using "`pathtab'/first_col_pell_state.dta";
drop if _merge==2;
drop _merge;

/*Merging data with information with opening and closing dates for loans in the NSLDS*/
capture drop _merge;
sort pid;
merge m:1 pid using "`pathtab'/open_close_sl_accounts_nslds.dta";
drop if _merge==2;
drop _merge;

sort pid year;

/*******Age Variables********/;
merge m:1 pid using "`pathtab'/age_tomerge.dta";
drop _merge;

local var="1997 1999 2001 2003 2007 2010 2012 2014";
foreach i of local var {;
gen day_of_TU_`i'=mdy(6,30,`i');
format day_of_TU_`i' %td;
};
gen day_of_TU_2004=mdy(12,31,2004);
format day_of_TU_2004 %td;
gen day_of_TU_2008=mdy(12,31,2008);
format day_of_TU_2008 %td;


/*Age by year*/
gen age=age_2004+(day_of_TU_1997-day_of_TU_2004)/365 if year==1997;
replace age=age_2004+(day_of_TU_1999-day_of_TU_2004)/365 if year==1999;
replace age=age_2004+(day_of_TU_2001-day_of_TU_2004)/365 if year==2001;
replace age=age_2004+(day_of_TU_2003-day_of_TU_2004)/365 if year==2003;
replace age=age_2004+(day_of_TU_2004-day_of_TU_2004)/365 if year==2004;
replace age=age_2004+(day_of_TU_2007-day_of_TU_2004)/365 if year==2007;
replace age=age_2004+(day_of_TU_2008-day_of_TU_2004)/365 if year==2008;
replace age=age_2004+(day_of_TU_2010-day_of_TU_2004)/365 if year==2010;
replace age=age_2004+(day_of_TU_2012-day_of_TU_2004)/365 if year==2012;
replace age=age_2004+(day_of_TU_2014-day_of_TU_2004)/365 if year==2014;


/**************************
New Variables that indicate if the person has open student loans
*with_sl_debt_yo  indicates if the person had student debt at age yo
*sl_debt_yo indicates the level of debt at age yo
***************************/
/*********************
Before creating these variables, I'm going to eliminate cases where student loans
where opened for less than 120*/
forvalues i=1(1)6 {;
gen dif`i'=student_end`i'-student_start`i';
replace student_end`i'=. if dif`i'<=120;
replace student_start`i'=. if dif`i'<=120;
drop dif`i';
};
forvalues i=1(1)4 {;
gen dif`i'=nslds_end`i'-nslds_start`i';
replace nslds_end`i'=. if dif`i'<=120;
replace nslds_start`i'=. if dif`i'<=120;
drop dif`i';
};


sort pid year;
local var="student mortgage auto ccard_indiv_joint install lcred unsec secgoods lease";
forvalues  yo=22(1)41 {;
local yo_1=`yo'-1;
gen with_sl_debt_t_`yo'=0;
replace with_sl_debt_t_`yo'=1 if ((student_start1-dob)/365<`yo' & (student_end1-dob)/365>=`yo_1') |
((student_start2-dob)/365<`yo' & (student_end2-dob)/365>=`yo_1') |
((student_start3-dob)/365<`yo' & (student_end3-dob)/365>=`yo_1') |
((student_start4-dob)/365<`yo' & (student_end4-dob)/365>=`yo_1') |
((student_start5-dob)/365<`yo' & (student_end5-dob)/365>=`yo_1') |
((student_start6-dob)/365<`yo' & (student_end6-dob)/365>=`yo_1') |
((nslds_start1-dob)/365<`yo' & (nslds_end1-dob)/365>=`yo_1') |
((nslds_start2-dob)/365<`yo' & (nslds_end2-dob)/365>=`yo_1') |
((nslds_start3-dob)/365<`yo' & (nslds_end3-dob)/365>=`yo_1') |
((nslds_start4-dob)/365<`yo' & (nslds_end4-dob)/365>=`yo_1');
rename with_sl_debt_t_`yo' with_sl_debt_`yo';


gen with_auto_debt_t_`yo'=0;
replace with_auto_debt_t_`yo'=1 if ((auto_start1-dob)/365<`yo' & (auto_end1-dob)/365>=`yo_1') |
((auto_start2-dob)/365<`yo' & (auto_end2-dob)/365>=`yo_1') |
((auto_start3-dob)/365<`yo' & (auto_end3-dob)/365>=`yo_1') |
((auto_start4-dob)/365<`yo' & (auto_end4-dob)/365>=`yo_1') |
((auto_start5-dob)/365<`yo' & (auto_end5-dob)/365>=`yo_1') |
((auto_start6-dob)/365<`yo' & (auto_end6-dob)/365>=`yo_1') |
((auto_start7-dob)/365<`yo' & (auto_end7-dob)/365>=`yo_1') |
((auto_start8-dob)/365<`yo' & (auto_end8-dob)/365>=`yo_1') |
((auto_start9-dob)/365<`yo' & (auto_end9-dob)/365>=`yo_1');
rename with_auto_debt_t_`yo' with_auto_debt_`yo';

gen with_ccard_debt_t_`yo'=0;
replace with_ccard_debt_t_`yo'=1 if ((ccard_start1-dob)/365<`yo' & (ccard_end1-dob)/365>=`yo_1') |
((ccard_start2-dob)/365<`yo' & (ccard_end2-dob)/365>=`yo_1') |
((ccard_start3-dob)/365<`yo' & (ccard_end3-dob)/365>=`yo_1') |
((ccard_start4-dob)/365<`yo' & (ccard_end4-dob)/365>=`yo_1') |
((ccard_start5-dob)/365<`yo' & (ccard_end5-dob)/365>=`yo_1') |
((ccard_start6-dob)/365<`yo' & (ccard_end6-dob)/365>=`yo_1') |
((ccard_start7-dob)/365<`yo' & (ccard_end7-dob)/365>=`yo_1') |
((ccard_start8-dob)/365<`yo' & (ccard_end8-dob)/365>=`yo_1');
rename with_ccard_debt_t_`yo' with_ccard_debt_`yo';

gen with_install_debt_t_`yo'=0;
replace with_install_debt_t_`yo'=1 if ((install_start1-dob)/365<`yo' & (install_end1-dob)/365>=`yo_1') |
((install_start2-dob)/365<`yo' & (install_end2-dob)/365>=`yo_1') |
((install_start3-dob)/365<`yo' & (install_end3-dob)/365>=`yo_1') |
((install_start4-dob)/365<`yo' & (install_end4-dob)/365>=`yo_1') |
((install_start5-dob)/365<`yo' & (install_end5-dob)/365>=`yo_1') |
((install_start6-dob)/365<`yo' & (install_end6-dob)/365>=`yo_1') |
((install_start7-dob)/365<`yo' & (install_end7-dob)/365>=`yo_1');
rename with_install_debt_t_`yo' with_install_debt_`yo';

gen with_lcred_debt_t_`yo'=0;
replace with_lcred_debt_t_`yo'=1 if ((lcred_start1-dob)/365<`yo' & (lcred_end1-dob)/365>=`yo_1') |
((lcred_start2-dob)/365<`yo' & (lcred_end2-dob)/365>=`yo_1') |
((lcred_start3-dob)/365<`yo' & (lcred_end3-dob)/365>=`yo_1') |
((lcred_start4-dob)/365<`yo' & (lcred_end4-dob)/365>=`yo_1') |
((lcred_start5-dob)/365<`yo' & (lcred_end5-dob)/365>=`yo_1');
rename with_lcred_debt_t_`yo' with_lcred_debt_`yo';

gen with_unsec_debt_t_`yo'=0;
replace with_unsec_debt_t_`yo'=1 if ((unsec_start1-dob)/365<`yo' & (unsec_end1-dob)/365>=`yo_1') |
((unsec_start2-dob)/365<`yo' & (unsec_end2-dob)/365>=`yo_1') |
((unsec_start3-dob)/365<`yo' & (unsec_end3-dob)/365>=`yo_1') |
((unsec_start4-dob)/365<`yo' & (unsec_end4-dob)/365>=`yo_1') |
((unsec_start5-dob)/365<`yo' & (unsec_end5-dob)/365>=`yo_1') |
((unsec_start6-dob)/365<`yo' & (unsec_end6-dob)/365>=`yo_1') |
((unsec_start7-dob)/365<`yo' & (unsec_end7-dob)/365>=`yo_1') |
((unsec_start8-dob)/365<`yo' & (unsec_end8-dob)/365>=`yo_1') |
((unsec_start9-dob)/365<`yo' & (unsec_end9-dob)/365>=`yo_1') |
((unsec_start10-dob)/365<`yo' & (unsec_end10-dob)/365>=`yo_1');
rename with_unsec_debt_t_`yo' with_unsec_debt_`yo';

gen with_secgoods_debt_t_`yo'=0;
replace with_secgoods_debt_t_`yo'=1 if ((secgoods_start1-dob)/365<`yo' & (secgoods_end1-dob)/365>=`yo_1') |
((secgoods_start2-dob)/365<`yo' & (secgoods_end2-dob)/365>=`yo_1') |
((secgoods_start3-dob)/365<`yo' & (secgoods_end3-dob)/365>=`yo_1') |
((secgoods_start4-dob)/365<`yo' & (secgoods_end4-dob)/365>=`yo_1') |
((secgoods_start5-dob)/365<`yo' & (secgoods_end5-dob)/365>=`yo_1') |
((secgoods_start6-dob)/365<`yo' & (secgoods_end6-dob)/365>=`yo_1') |
((secgoods_start7-dob)/365<`yo' & (secgoods_end7-dob)/365>=`yo_1') |
((secgoods_start8-dob)/365<`yo' & (secgoods_end8-dob)/365>=`yo_1') |
((secgoods_start9-dob)/365<`yo' & (secgoods_end9-dob)/365>=`yo_1');
rename with_secgoods_debt_t_`yo' with_secgoods_debt_`yo';

gen with_lease_debt_t_`yo'=0;
replace with_lease_debt_t_`yo'=1 if ((lease_start1-dob)/365<`yo' & (lease_end1-dob)/365>=`yo_1') |
((lease_start2-dob)/365<`yo' & (lease_end2-dob)/365>=`yo_1') |
((lease_start3-dob)/365<`yo' & (lease_end3-dob)/365>=`yo_1') |
((lease_start4-dob)/365<`yo' & (lease_end4-dob)/365>=`yo_1');
rename with_lease_debt_t_`yo' with_lease_debt_`yo';

gen with_mortgage_debt_t_`yo'=0;
replace with_mortgage_debt_t_`yo'=1 if ((mortgage_start1-dob)/365<`yo' & (mortgage_end1-dob)/365>=`yo_1') |
((mortgage_start2-dob)/365<`yo' & (mortgage_end2-dob)/365>=`yo_1') |
((mortgage_start3-dob)/365<`yo' & (mortgage_end3-dob)/365>=`yo_1') |
((mortgage_start4-dob)/365<`yo' & (mortgage_end4-dob)/365>=`yo_1') |
((mortgage_start5-dob)/365<`yo' & (mortgage_end5-dob)/365>=`yo_1') |
((mortgage_start6-dob)/365<`yo' & (mortgage_end6-dob)/365>=`yo_1') |
((mortgage_start7-dob)/365<`yo' & (mortgage_end7-dob)/365>=`yo_1');
rename with_mortgage_debt_t_`yo' with_mortgage_debt_`yo';

gen fico_t_`yo'=0;
replace fico_t_`yo'=tr_am if age>=`yo_1' & age<`yo';
by pid: egen fico_`yo'=max(fico_t_`yo');
drop fico_t_`yo';

gen sl_debt_t_`yo'=0;
replace sl_debt_t_`yo'=student_debt if age>=`yo_1' & age<`yo';
by pid: egen sl_debt_`yo'=max(sl_debt_t_`yo');
drop sl_debt_t_`yo';

gen auto_debt_t_`yo'=0;
replace auto_debt_t_`yo'=auto_debt if age>=`yo_1' & age<`yo';
by pid: egen auto_debt_`yo'=max(auto_debt_t_`yo');
drop auto_debt_t_`yo';

gen ccard_debt_t_`yo'=0;
replace ccard_debt_t_`yo'=ccard_indiv_joint_debt if age>=`yo_1' & age<`yo';
by pid: egen ccard_debt_`yo'=max(ccard_debt_t_`yo');
drop ccard_debt_t_`yo';

gen mortgage_debt_t_`yo'=0;
replace mortgage_debt_t_`yo'=mortgage_debt if age>=`yo_1' & age<`yo';
by pid: egen mortgage_debt_`yo'=max(mortgage_debt_t_`yo');
drop mortgage_debt_t_`yo';

gen ccard_lim_t_`yo'=0;
replace ccard_lim_t_`yo'=ccard_indiv_joint_lim if age>=`yo_1' & age<`yo';
by pid: egen ccard_lim_`yo'=max(ccard_lim_t_`yo');
drop ccard_lim_t_`yo';

gen ccard_lim_nm_t_`yo'=0;
replace ccard_lim_nm_t_`yo'=ccard_indiv_joint_lim_nm if age>=`yo_1' & age<`yo';
by pid: egen ccard_lim_nm_`yo'=max(ccard_lim_nm_t_`yo');
drop ccard_lim_nm_t_`yo';

gen install_debt_t_`yo'=0;
replace install_debt_t_`yo'=install_debt if age>=`yo_1' & age<`yo';
by pid: egen install_debt_`yo'=max(install_debt_t_`yo');
drop install_debt_t_`yo';

gen lcred_debt_t_`yo'=0;
replace lcred_debt_t_`yo'=lcred_debt if age>=`yo_1' & age<`yo';
by pid: egen lcred_debt_`yo'=max(lcred_debt_t_`yo');
drop lcred_debt_t_`yo';

gen unsec_debt_t_`yo'=0;
replace unsec_debt_t_`yo'=unsec_debt if age>=`yo_1' & age<`yo';
by pid: egen unsec_debt_`yo'=max(unsec_debt_t_`yo');
drop unsec_debt_t_`yo';

gen secgoods_debt_t_`yo'=0;
replace secgoods_debt_t_`yo'=secgoods_debt if age>=`yo_1' & age<`yo';
by pid: egen secgoods_debt_`yo'=max(secgoods_debt_t_`yo');
drop secgoods_debt_t_`yo';

gen lease_debt_t_`yo'=0;
replace lease_debt_t_`yo'=lease_debt if age>=`yo_1' & age<`yo';
by pid: egen lease_debt_`yo'=max(lease_debt_t_`yo');
drop lease_debt_t_`yo';

gen total_debt_`yo'=sl_debt_`yo'+auto_debt_`yo'+ccard_debt_`yo'+install_debt_`yo'+lcred_debt_`yo'+unsec_debt_`yo'+secgoods_debt_`yo'+lease_debt_`yo';

/*Expressing debts in 1,000 of dollars*/
replace sl_debt_`yo'=sl_debt_`yo'/1000;
replace auto_debt_`yo'=auto_debt_`yo'/1000;
replace ccard_debt_`yo'=ccard_debt_`yo'/1000;
replace total_debt_`yo'=total_debt_`yo'/1000;
replace mortgage_debt_`yo'=mortgage_debt_`yo'/1000;
replace ccard_lim_`yo'=ccard_lim_`yo'/1000;
replace ccard_lim_nm_`yo'=ccard_lim_nm_`yo'/1000;
replace install_debt_`yo'=install_debt_`yo'/1000;
replace lcred_debt_`yo'=lcred_debt_`yo'/1000;
replace unsec_debt_`yo'=unsec_debt_`yo'/1000;
replace secgoods_debt_`yo'=secgoods_debt_`yo'/1000;
replace lease_debt_`yo'=lease_debt_`yo'/1000;

/*There are lots of missing values of debt levels because we don't observe individuals at a particular age. I will create a variable that indicates if we observe an individual at a particular age*/
gen noage_`yo't=0;
replace noage_`yo't=1 if age>=`yo_1' & age<`yo';
by pid: egen noage_`yo'tt=sum(noage_`yo't);
gen noage_`yo'=(noage_`yo'tt==0);
drop noage_`yo't noage_`yo'tt; 

/*Delinquencies*/
foreach debt of local var {;
gen del_`debt'120_t_`yo'=0;
replace del_`debt'120_t_`yo'=1 if (`debt'_rec_120plus~=. | `debt'_rec_worse~=. | (`debt'_hist_120plus>0 & `debt'_hist_120plus<.)) & age<`yo';
by pid: egen del_`debt'120_`yo'=max(del_`debt'120_t_`yo');
gen del_`debt'90_t_`yo'=0;
replace del_`debt'90_t_`yo'=1 if (`debt'_rec_90120~=. | (`debt'_hist_90120>0 & `debt'_hist_90120<.) | del_`debt'120_t_`yo'==1) & age<`yo';
by pid: egen del_`debt'90_`yo'=max(del_`debt'90_t_`yo');
gen del_`debt'60_t_`yo'=0;
replace del_`debt'60_t_`yo'=1 if (`debt'_rec_6090~=. | (`debt'_hist_6090>0 & `debt'_hist_6090<.) | del_`debt'90_t_`yo'==1) & age<`yo';
by pid: egen del_`debt'60_`yo'=max(del_`debt'60_t_`yo');
gen del_`debt'30_t_`yo'=0;
replace del_`debt'30_t_`yo'=1 if (`debt'_rec_3060~=. | (`debt'_hist_3060>0 & `debt'_hist_3060<.) | del_`debt'60_t_`yo'==1) & age<`yo';
by pid: egen del_`debt'30_`yo'=max(del_`debt'30_t_`yo');

drop del_`debt'120_t_`yo' del_`debt'90_t_`yo' del_`debt'60_t_`yo' del_`debt'30_t_`yo';
};

rename del_student120_`yo' del_sl_debt_120_`yo_1';
rename del_student90_`yo' del_sl_debt_90_`yo_1';
rename del_student60_`yo' del_sl_debt_60_`yo_1';
rename del_student30_`yo' del_sl_debt_30_`yo_1';

rename del_auto120_`yo' del_auto_debt_120_`yo_1';
rename del_auto90_`yo' del_auto_debt_90_`yo_1';
rename del_auto60_`yo' del_auto_debt_60_`yo_1';
rename del_auto30_`yo' del_auto_debt_30_`yo_1';

rename del_ccard_indiv_joint120_`yo' del_ccard_debt_120_`yo_1';
rename del_ccard_indiv_joint90_`yo' del_ccard_debt_90_`yo_1';
rename del_ccard_indiv_joint60_`yo' del_ccard_debt_60_`yo_1';
rename del_ccard_indiv_joint30_`yo' del_ccard_debt_30_`yo_1';

rename del_install120_`yo' del_install_debt_120_`yo_1';
rename del_install90_`yo' del_install_debt_90_`yo_1';
rename del_install60_`yo' del_install_debt_60_`yo_1';
rename del_install30_`yo' del_install_debt_30_`yo_1';

rename del_lcred120_`yo' del_lcred_debt_120_`yo_1';
rename del_lcred90_`yo' del_lcred_debt_90_`yo_1';
rename del_lcred60_`yo' del_lcred_debt_60_`yo_1';
rename del_lcred30_`yo' del_lcred_debt_30_`yo_1';

rename del_unsec120_`yo' del_unsec_debt_120_`yo_1';
rename del_unsec90_`yo' del_unsec_debt_90_`yo_1';
rename del_unsec60_`yo' del_unsec_debt_60_`yo_1';
rename del_unsec30_`yo' del_unsec_debt_30_`yo_1';

rename del_secgoods120_`yo' del_secgoods_debt_120_`yo_1';
rename del_secgoods90_`yo' del_secgoods_debt_90_`yo_1';
rename del_secgoods60_`yo' del_secgoods_debt_60_`yo_1';
rename del_secgoods30_`yo' del_secgoods_debt_30_`yo_1';

rename del_lease120_`yo' del_lease_debt_120_`yo_1';
rename del_lease90_`yo' del_lease_debt_90_`yo_1';
rename del_lease60_`yo' del_lease_debt_60_`yo_1';
rename del_lease30_`yo' del_lease_debt_30_`yo_1';

rename del_mortgage120_`yo' del_mortgage_debt_120_`yo_1';
rename del_mortgage90_`yo' del_mortgage_debt_90_`yo_1';
rename del_mortgage60_`yo' del_mortgage_debt_60_`yo_1';
rename del_mortgage30_`yo' del_mortgage_debt_30_`yo_1';

rename with_sl_debt_`yo' with_sl_debt_`yo_1';
rename with_auto_debt_`yo' with_auto_debt_`yo_1';
rename with_ccard_debt_`yo' with_ccard_debt_`yo_1';
rename with_install_debt_`yo' with_install_debt_`yo_1';
rename with_lcred_debt_`yo' with_lcred_debt_`yo_1';
rename with_unsec_debt_`yo' with_unsec_debt_`yo_1';
rename with_secgoods_debt_`yo' with_secgoods_debt_`yo_1';
rename with_lease_debt_`yo' with_lease_debt_`yo_1';
rename with_mortgage_debt_`yo' with_mortgage_debt_`yo_1';

rename sl_debt_`yo' sl_debt_`yo_1';
rename auto_debt_`yo' auto_debt_`yo_1';
rename ccard_debt_`yo' ccard_debt_`yo_1';
rename install_debt_`yo' install_debt_`yo_1';
rename lcred_debt_`yo' lcred_debt_`yo_1';
rename unsec_debt_`yo' unsec_debt_`yo_1';
rename secgoods_debt_`yo' secgoods_debt_`yo_1';
rename lease_debt_`yo' lease_debt_`yo_1';
rename mortgage_debt_`yo' mortgage_debt_`yo_1';
rename total_debt_`yo' total_debt_`yo_1';
rename fico_`yo' fico_`yo_1';
rename noage_`yo' noage_`yo_1';
rename ccard_lim_`yo' ccard_lim_`yo_1';
rename ccard_lim_nm_`yo' ccard_lim_nm_`yo_1'; 

/*Defaults*/
gen def_sl_debt_`yo'=0;
replace def_sl_debt_`yo'=1 if (defaulted_dt-dob)/365<=`yo';

rename def_sl_debt_`yo' def_sl_debt_`yo_1';
};


/**************
Now I merge the dataset with degrees and majors
**************/

merge m:1 pid using "`pathtab'/degree_major_tomerge.dta";
drop _merge;

forvalues yo=21(1)40 {;
replace degree_`yo'=0 if degree_`yo'==.;
replace major_`yo'=0 if major_`yo'==.;
replace major_agg_`yo'=0 if major_agg_`yo'==.;
};


/**************
Now I merge the dataset with total federal loans disbursed
**************/
merge m:1 pid using "`pathtab'/tot_fedloans.dta";
drop _merge;

forvalues yo=22(1)34 {;
replace tot_fedloans_`yo'=0 if tot_fedloans_`yo'==.;
/*Expressing debt in 1,000 of dollars*/
replace tot_fedloans_`yo'=tot_fedloans_`yo'/1000;
};


/****************************/;
/*New definition of sector*/
/****************************/;
merge m:1 pid using "`pathtab'/sector.dta";
drop _merge;
forvalues yo=21(1)40 {;
replace ever_with_pub4_`yo'=0 if ever_with_pub4_`yo'==.;
replace ever_with_pub2_`yo'=0 if ever_with_pub2_`yo'==.;
replace ever_with_priv_nfp4_`yo'=0 if ever_with_priv_nfp4_`yo'==.;
replace ever_with_priv_nfp2_`yo'=0 if ever_with_priv_nfp2_`yo'==.;
replace ever_with_priv_fp_`yo'=0 if ever_with_priv_fp_`yo'==.;
};


/**********************
Pell Grant Variables
*pell_yo
*pell_amount_yo
***********************/
merge m:1 pid using "`pathtab'/pell_variables.dta";
drop _merge;
forvalues yo=21(1)34 {;
replace pell_`yo'=0 if pell_`yo'==.;
replace pell_amount_`yo'=0 if pell_amount_`yo'==.;
/*Expressing Pell received in 1,000 of dollars*/
replace pell_amount_`yo'=pell_amount_`yo'/1000;
};

keep pid year day_of_TU* 
in_nsc age tr_am  student_debt
tract county state state_county missing_state missing_county bg zip cmsa msa avg_agi* county tract bg
student_start* student_end* nslds_start* nslds_end*
mortgage_start* mortgage_end*
ccard_start* ccard_end*
auto_start* auto_end* install_start* install_end* lcred_start* lcred_end* unsec_start* unsec_end* secgoods_start* secgoods_end* lease_start* lease_end*
dob dob_y dob_m
with_sl_debt_2* with_sl_debt_3*  with_sl_debt_4* 
with_auto_debt_2* with_auto_debt_3* with_auto_debt_4* 
with_ccard_debt_2* with_ccard_debt_3* with_ccard_debt_4*
with_install_debt_2* with_install_debt_3* with_install_debt_4* 
with_lcred_debt_2* with_lcred_debt_3* with_lcred_debt_4* 
with_unsec_debt_2* with_unsec_debt_3* with_unsec_debt_4* 
with_secgoods_debt_2* with_secgoods_debt_3* with_secgoods_debt_4* 
with_lease_debt_2* with_lease_debt_3* with_lease_debt_4* 
with_mortgage_debt_2* with_mortgage_debt_3* with_mortgage_debt_4* 
fico_2* fico_3*  fico_4* ccard_lim_2* ccard_lim_3* ccard_lim_4* ccard_lim_nm_2* ccard_lim_nm_3* ccard_lim_nm_4*
sl_debt_2* sl_debt_3* sl_debt_4* auto_debt_2* auto_debt_3* auto_debt_4*
install_debt_2* install_debt_3* install_debt_4*
lcred_debt_2* lcred_debt_3* lcred_debt_4*
unsec_debt_2* unsec_debt_3* unsec_debt_4*
secgoods_debt_2* secgoods_debt_3* secgoods_debt_4*
lease_debt_2* lease_debt_3* lease_debt_4* 
mortgage_debt_2* mortgage_debt_3* mortgage_debt_4*
ccard_debt_2* ccard_debt_3* ccard_debt_4* total_debt_2* total_debt_3* total_debt_4*
del_sl_debt_*_2* del_sl_debt_*_3*  del_sl_debt_*_4* del_auto_debt_*_2* del_auto_debt_*_3* del_auto_debt_*_4* del_ccard_debt_*_2* del_ccard_debt_*_3* del_ccard_debt_*_4*
del_install_debt_*_2* del_install_debt_*_3* del_install_debt_*_4*
del_lcred_debt_*_2* del_lcred_debt_*_3* del_lcred_debt_*_4*
del_unsec_debt_*_2* del_unsec_debt_*_3* del_unsec_debt_*_4*
del_secgoods_debt_*_2* del_secgoods_debt_*_3* del_secgoods_debt_*_4*
del_lease_debt_*_2* del_lease_debt_*_3* del_lease_debt_*_4*
del_mortgage_debt_*_2* del_mortgage_debt_*_3* del_mortgage_debt_*_4*
def_sl_debt_2* def_sl_debt_3* def_sl_debt_4*
noage_2* noage_3* noage_4*
degree_2* degree_3* degree_4* major_2* degree_3* degree_4* major_agg_2* major_agg_3* major_agg_4*
tot_fedloans_2* tot_fedloans_3*
ever_with_pub4_2* ever_with_pub4_3* ever_with_pub4_4*
ever_with_pub2_2* ever_with_pub2_3* ever_with_pub2_4*
ever_with_priv_nfp4_2* ever_with_priv_nfp4_3* ever_with_priv_nfp4_4*
ever_with_priv_nfp2_2* ever_with_priv_nfp2_3*  ever_with_priv_nfp2_4*
ever_with_priv_fp_2* ever_with_priv_fp_3*  ever_with_priv_fp_4*
pell_2* pell_3* pell_amount_2* pell_amount_3*


mortgage_debt
/*hs_state sexsat latstsat satvrecn satmrecn maxsat year_hsgrad grdesat motheduc fatheduc*/
first_col_enroll_nsc first_col_state_nsc first_col_from_grad_nsc first_col_opeid_nsc last_col_enroll_nsc last_col_opeid_nsc
first_col_enroll_nslds first_col_state_nslds first_col_state_fips_nslds first_col_sector_nslds first_col_opeid_nslds last_col_enroll_nslds last_col_opeid_nslds
award_yr first_col_state_pell first_col_state_fips_pell first_col_sector_pell first_col_opeid_pell; 


rename award_yr award_yr_pell;

replace state="" if state=="0" | state==".";
gen state_t="AK" if state=="02";
replace state_t="AL" if state=="01";
replace state_t="AR" if state=="05";
replace state_t="AZ" if state=="04";
replace state_t="CA" if state=="06";
replace state_t="CO" if state=="08";
replace state_t="CT" if state=="09";
replace state_t="DC" if state=="11";
replace state_t="DE" if state=="10";
replace state_t="FL" if state=="12";
replace state_t="GA" if state=="13";
replace state_t="HI" if state=="15";
replace state_t="IA" if state=="19";
replace state_t="ID" if state=="16";
replace state_t="IL" if state=="17";
replace state_t="IN" if state=="18";
replace state_t="KS" if state=="20";
replace state_t="KY" if state=="21";
replace state_t="LA" if state=="22";
replace state_t="MA" if state=="25";
replace state_t="MD" if state=="24";
replace state_t="ME" if state=="23";
replace state_t="MI" if state=="26";
replace state_t="MN" if state=="27";
replace state_t="MS" if state=="28";
replace state_t="MO" if state=="29";
replace state_t="MT" if state=="30";
replace state_t="NC" if state=="37";
replace state_t="ND" if state=="38";
replace state_t="NE" if state=="31";
replace state_t="NH" if state=="33";
replace state_t="NJ" if state=="34";
replace state_t="NM" if state=="35";
replace state_t="NV" if state=="32";
replace state_t="NY" if state=="36";
replace state_t="OH" if state=="39";
replace state_t="OK" if state=="40";
replace state_t="OR" if state=="41";
replace state_t="PA" if state=="42";
replace state_t="PR" if state=="72";
replace state_t="RI" if state=="44";
replace state_t="SC" if state=="45";
replace state_t="SD" if state=="46";
replace state_t="TN" if state=="47";
replace state_t="TX" if state=="48";
replace state_t="UT" if state=="49";
replace state_t="VA" if state=="51";
replace state_t="VT" if state=="50";
replace state_t="WA" if state=="53";
replace state_t="WI" if state=="55";
replace state_t="WV" if state=="54";
replace state_t="WY" if state=="56";

rename state state_fips;
rename state_t state;

compress;
save "`out'/dataset_for_regs_RandR_noCB.dta", replace;


